package com.study.manage.dal;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import com.study.manage.domain.EvaluateApplyWork;
import com.study.manage.domain.enumm.LiveDieState;

public interface EvaluateApplyWorkRepository
        extends PagingAndSortingRepository<EvaluateApplyWork, Long>,
        JpaSpecificationExecutor<EvaluateApplyWork> {

    EvaluateApplyWork findByEvaluateApplyIdAndState(Long evaluateApplyId, LiveDieState state);

    List<EvaluateApplyWork> findByEvaluateApplyIdAndUserIdAndState(Long evaluateApplyId,
            Long userId, LiveDieState state);


    int countByEvaluateIdAndState(@Param("evaluateId") Long evaluateId,
            @Param("state") LiveDieState state);

    EvaluateApplyWork findByEvaluateApplyId(Long evaluateApplyId);

    @Query("select count(t.id) from EvaluateApplyWork t where t.state!=1 "
            + "and t.evaluateId in (:evaluateIds) "
            + "and (to_days(createdAt)=to_days(:date) or :date is null)")
    int countByEvaluateIdInAndDay(@Param("evaluateIds") List<Long> evaluateIds,
            @Param("date") Date date);

    @Query("select count(t.id) from EvaluateApplyWork t, EvaluateApply e where t.evaluateApplyId=e.id and t.state!=1 "
            + "and t.evaluateId in (:evaluateIds) and (e.referenceId=:referenceId or :referenceId is null) "
            + "and (to_days(t.createdAt)=to_days(:date) or :date is null)")
    int countByReferenceId(@Param("evaluateIds") List<Long> evaluateIds, @Param("date") Date date,
            @Param("referenceId") Long referenceId);

    @Query("select t.createdAt,count(t.id) from EvaluateApplyWork t  where t.state!=1 and t.evaluateId in (:evaluateIds) "
            + "and to_days(t.createdAt)>=to_days(:beginAt) and to_days(t.createdAt)<=to_days(:endAt) group by to_days(t.createdAt)")
    List<Object[]> countByEvaluateIdInGroupByDay(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);

    @Query(nativeQuery = true,
            value = "select t.created_at,count(t.id),'作品帖数（最终）' as type from sa_evaluate_apply_work as t  where t.state=0 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,count(t.id),'作品帖数（所有）' as type from sa_blog as t left join sa_evaluate_apply as ea on t.ext_id=ea.id where t.state=0 and ea.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,count(DISTINCT(t.id)),'评委点评作品数' as type from sa_blog as t "
                    + "left join sa_evaluate_apply as ea on t.ext_id=ea.id "
                    + "left join sa_blog_comment as bc on bc.blog_id=t.id and bc.state=0 "
                    + "left join sa_evaluate_jury as ej on ej.teacher_id=bc.user_id "
                    + "and ej.evaluate_id=ea.evaluate_id "
                    + "where t.state=0 and bc.id is not null and ej.id is not null and ea.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,count(DISTINCT(t.id)),'评委打分作品数' as type from sa_blog as t "
                    + "left join sa_evaluate_apply as ea on t.ext_id=ea.id "
                    + "left join sa_blog_grade as bg on bg.blog_id=t.id and bg.is_evaluate_jury=0 "
                    + "where t.state=0 and bg.id is not null and ea.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)")
    List<Object[]> countByEvaluateIdInGroupByDays(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);

}
